﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace 药品信息管理
{
    public partial class YP_CKDCX : Form
    {
        public YP_CKDCX()
        {
            InitializeComponent();
        }

        protected override bool ProcessDialogKey(Keys keyData)
        {
            if ((ActiveControl is TextBox || ActiveControl is Button) && keyData == Keys.Enter)
                keyData = Keys.Tab;
            return base.ProcessDialogKey(keyData);
        }


        private void YP_CKDCX_Load_1(object sender, EventArgs e)
        {
            #region 退药科室，供应商下拉框数据绑定
            getSqlConnection myconnection = new 药品信息管理.getSqlConnection();
            SqlConnection conn = myconnection.GetCon();
            SqlCommand mycomm = new SqlCommand("select mc from zd_ks", conn);
            SqlDataReader myread = mycomm.ExecuteReader();
            while (myread.Read())
            {
                this.comboBoxEx1.Items.Add((string)myread[0]);

            }
            conn.Close();

            #endregion 

            #region 显示出入库类型
            int y = 20;
            db db1 = new db();
            DataTable dt = db1.sqldt("select  mc from yk_crlx");
            foreach (DataRow dr in dt.Rows)
            {
                CheckBox cb = new CheckBox();
                cb.Location = new Point(6, y);
                y = y + 23;
                cb.Text = dr["mc"].ToString();
                cb.Checked = true; cb.Size = new Size(180,16);
                panelEx1.Controls.Add(cb);
                //groupBox2.Controls.Add(cb);
            }
            #endregion 

            dateTimePicker1＿.Value = DateTime.Now.AddDays(-(DateTime.Now.Day-1));
            btn_Refresh_Click(sender, e);
            #region 显示出库单0表内容
//            db db2 = new db();
//            DataTable dt2 = db2.sqldt(@"SELECT C0.XH 序号 ,C0.CKDH 出库单号,convert(datetime,C0.RQ-2) 制单日期,KS.MC 供应商或退药科室,CR.MC 类型,C0.SHRY 审核人员,C0.SHRY 记账人员,HJ.JJJE 进价金额, HJ.LSJE 零售金额, HJ.YPCOUNT 药品种量
//                           FROM YK_CKD0 C0
//                                LEFT JOIN YK_CRLX CR ON C0.CKLX = CR.BM
//                                LEFT JOIN ZD_KS KS ON C0.KSBM=KS.BM 
//                                LEFT JOIN (SELECT CKDH, SUM(CBJE) AS JJJE, SUM(JE) AS LSJE,COUNT(*) AS YPCOUNT FROM YK_CKD1 GROUP BY CKDH) HJ  ON C0.CKDH = HJ.CKDH 
//                                ORDER BY C0.CKDH");

//            dataGridViewX1.DataSource = dt2;
          #endregion
        }

        private void comboBoxEx1_KeyDown(object sender, KeyEventArgs e)
        {

        }

        

        private void btn_Refresh_Click(object sender, EventArgs e)
        {
            db db1 = new db();
            string time1 = dateTimePicker1＿.Value.ToString("yyyy.MM.dd"), time2 = dateTimePicker2.Value.ToString("yyyy.MM.dd");//调用定义方法将时间转换和定义。
            string string_ckdh1 = "", string_ckdh2 = "", string_ks = "", string_cklx = " and CR.MC in ('') ", string_flag = " 状态 in ('') ";//用来筛选出库单号的SQL条件定义。
            if (textBoxX1.Text != "") { string_ckdh1 = " and C0.ckdh>=" + textBoxX1.Text + " "; }//出库单号不为空时查询的最小单号
            if (textBoxX2.Text != "") { string_ckdh2 = " and C0.ckdh<=" + textBoxX2.Text + " "; }//出库单号不为空时查询的做大单号
            if (comboBoxEx1.SelectedText != "") { string_ks = " and KS.MC='" + comboBoxEx1.SelectedText + "' "; }//科室选择不为空时的查询条件
            //if (comboBoxEx2.SelectedText != "") { string_gys = " and C0.gys =(select bm from YK_ZDGYS where mc='" + comboBoxEx1.SelectedText + "') "; }
           
            foreach (Control aa in panelEx1.Controls)//出入类型初始查询值
            {
                CheckBox cb = (CheckBox)aa;
                    if (cb.Checked == true)
                    {
                        string_cklx = string_cklx.Replace("''","'','"+cb.Text+"'");
                    }
                
            }


            foreach (Control aa in groupBox1.Controls)//出入库状态查询初始值
            {
                CheckBox cb = (CheckBox)aa;
                if (cb.Checked == true)
                {
                    string_flag = string_flag.Replace("''", "'','" + cb.Text + "'");
                }

            }

            string str = @"SELECT C0.XH 序号 ,C0.CKDH 出库单号,convert(datetime,C0.RQ-2) 制单日期,KS.MC 出库科室,CR.MC 类型,C0.SHRY 审核人员,
                           C0.SHRY 记账人员,HJ.JJJE 进价金额, HJ.LSJE 零售金额, HJ.YPCOUNT 药品种量,
                          (case when (C0.FLAG &2=2) then '已记账' when (C0.FLAG &1=1) then '已审核' else '未审核' end ) 状态
                           FROM YK_CKD0 C0
                                LEFT JOIN YK_CRLX CR ON C0.CKLX = CR.BM
                                LEFT JOIN ZD_KS KS ON C0.KSBM=KS.BM
                                LEFT JOIN (SELECT CKDH, SUM(CBJE) AS JJJE, SUM(JE) AS LSJE,COUNT(*) AS YPCOUNT FROM YK_CKD1 GROUP BY CKDH) HJ  ON C0.CKDH = HJ.CKDH 
                           WHERE ((C0.FLAG & 3 = 0) OR (C0.FLAG & 3 = 1) OR (C0.FLAG & 2 > 0)) AND C0.CKLX IN (-1,11,12,13,14,16)
                                AND C0.RQ >= " + db1.datetoint(time1) + " AND C0.RQ <= " + db1.datetoint(time2) + string_ckdh1 + string_ckdh2 + string_ks + string_cklx;
            str = "select * from ("+str+") a where "+string_flag+"order by 出库单号";
            dataGridViewX1.DataSource = db1.sqldt(str);
        }


        private void dataGridViewX1_DoubleClick(object sender, EventArgs e)
        {
            
        }

        private void btn_bar1_exit_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void txt_bar1_ckdh1_InputTextChanged(object sender)
        {
            #region 筛选形式
//            try
//            {
//                using (SqlConnection con = new SqlConnection(SQL.conStr))
//                {
//                    DataTable dt_ckd0 = new DataTable();
//                    string sqlyj = @"SELECT C0.XH 序号,C0.CKDH 出库单号,convert(datetime,C0.RQ-2) 制单日期,KS.MC 供应商或退药科室,CR.MC 类型,C0.SHRY 审核人员,C0.SHRY 记账人员,HJ.JJJE 进价金额, HJ.LSJE 零售金额, HJ.YPCOUNT 药品种量
//                           FROM YK_CKD0 C0 
//                                        LEFT JOIN YK_CRLX CR ON C0.CKLX = CR.BM 
//                                        LEFT JOIN ZD_KS KS ON C0.KSBM=KS.BM 
//                                        LEFT JOIN (SELECT CKDH, SUM(CBJE) AS JJJE, SUM(JE) AS LSJE,COUNT(*) AS YPCOUNT FROM YK_CKD1 GROUP BY CKDH) HJ  ON C0.CKDH = HJ.CKDH 
//                                        WHERE  C0.XH LIKE '%" + txt_bar1_ckdh1.Text.ToString() + "%'";
//                    SqlCommand ckd0_dw = new SqlCommand(sqlyj, con);//sql语句
//                    da.SelectCommand = ckd0_dw;//设置为已实例化SqlDataAdapter的查询命令
//                    da.Fill(dt_ckd0);
//                    dataGridViewX1.DataSource = dt_ckd0;

//                }
//            }
//            catch (Exception e)
//            {
//                MessageBox.Show(e.ToString());
            //            }
            #endregion

            foreach (DataGridViewRow R1 in dataGridViewX1.Rows)
            {
                if (R1.Cells[1].Value.ToString() == txt_bar1_ckdh1.Text)
                {
                    R1.Selected = true;
                }
                else { R1.Selected = false; }
            }

        }

        private void txt_bar1_ckdh2_InputTextChanged(object sender)
        {
            #region
            //            try
//            {
//                using (SqlConnection con = new SqlConnection(SQL.conStr)) 
//                {
//                    DataTable dt_ckd0 = new DataTable();
//                    string sqlyj = @"SELECT C0.XH 序号,C0.CKDH 出库单号,convert(datetime,C0.RQ-2) 制单日期,KS.MC 供应商或退药科室,CR.MC 类型,C0.SHRY 审核人员,C0.SHRY 记账人员,HJ.JJJE 进价金额, HJ.LSJE 零售金额, HJ.YPCOUNT 药品种量
//                                     FROM YK_CKD0 C0 
//                                        LEFT JOIN YK_CRLX CR ON C0.CKLX = CR.BM 
//                                        LEFT JOIN ZD_KS KS ON C0.KSBM=KS.BM 
//                                        LEFT JOIN (SELECT CKDH, SUM(CBJE) AS JJJE, SUM(JE) AS LSJE,COUNT(*) AS YPCOUNT FROM YK_CKD1 GROUP BY CKDH) HJ  ON C0.CKDH = HJ.CKDH 
//                                     WHERE  C0.CKDH LIKE '%"+ txt_bar1_ckdh2.Text.ToString() + "%'";
//                    SqlCommand ckd0_dw = new SqlCommand(sqlyj, con);//sql语句
//                    da.SelectCommand = ckd0_dw;//设置为已实例化SqlDataAdapter的查询命令
//                    da.Fill(dt_ckd0);
//                    dataGridViewX1.DataSource = dt_ckd0;

//                }
//            }
//            catch (Exception e)
//            {
//                MessageBox.Show(e.ToString());
            //            }
            #endregion 

            foreach (DataGridViewRow R2 in dataGridViewX1.Rows)
            {
                if (R2.Cells[0].Value.ToString() ==txt_bar1_ckdh2.Text)
                {
                    R2.Selected = true;
                }
                else { R2.Selected = false; }
            }


        }

        private void dataGridViewX1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            YP_CKDCX_MX ck1 = new YP_CKDCX_MX();
            ck1.ckdh = dataGridViewX1.Rows[e.RowIndex].Cells[1].Value.ToString();
            ck1.ShowDialog();
        }

        private void btn_daochu_Click(object sender, EventArgs e)
        {
            db db1 = new db();
            db1.DataToExcel(dataGridViewX1);
        }

        private void btn_Print_Click(object sender, EventArgs e)
        {
            dgVprint1.Print(dataGridViewX1,true);
        }


    }
}